"""
function:读取Excel根据部门分组，根据报表名称聚类，并按照相似度排序
author:wenzhengxing
date:2025/08/29 15:45
version:final
"""
import pandas as pd
import numpy as np
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
from sklearn.cluster import KMeans
import re
import string
from collections import defaultdict
import warnings

warnings.filterwarnings('ignore')


def preprocess_text(text):
    """预处理文本：小写化、去除标点、去除数字、去除多余空格"""
    if pd.isna(text):
        return "empty_text"
    text = str(text).lower()
    text = re.sub(f'[{re.escape(string.punctuation)}]', ' ', text)
    text = re.sub(r'\d+', ' ', text)
    text = re.sub(r'\s+', ' ', text).strip()
    return text if text else "empty_text"


def find_centroid_report(reports, vectorizer, cluster_vectors):
    """找到离聚类中心最近的报表名称"""
    if not reports or len(reports) == 0:
        return "无代表性报表"

    # 计算聚类中心
    centroid = np.mean(cluster_vectors, axis=0)

    # 计算每个报表与中心的距离
    distances = []
    for i, vec in enumerate(cluster_vectors):
        # 使用余弦相似度计算距离
        similarity = cosine_similarity([centroid], [vec])[0][0]
        distances.append((i, 1 - similarity))  # 将相似度转换为距离

    # 找到距离最小的报表
    min_idx = min(distances, key=lambda x: x[1])[0]
    return reports[min_idx]


def process_excel_with_clustering(file_path, sheet_name, department_col, report_col):
    """
    处理Excel文件：读取数据，按部门分组，按报表名称聚类，并将结果添加到新Sheet

    参数:
    file_path: Excel文件路径
    sheet_name: 工作表名称或索引
    department_col: 部门列名
    report_col: 报表名称列名

    返回:
    处理后的DataFrame
    """
    # 读取Excel文件
    try:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        print(f"成功读取Excel文件，共{len(df)}行数据")
    except Exception as e:
        print(f"读取Excel文件时出错: {e}")
        return None

    # 检查必要的列是否存在
    if department_col not in df.columns:
        print(f"Excel文件中没有找到'{department_col}'列")
        print(f"可用列: {list(df.columns)}")
        return None

    if report_col not in df.columns:
        print(f"Excel文件中没有找到'{report_col}'列")
        print(f"可用列: {list(df.columns)}")
        return None

    # 预处理报表名称
    # df['processed_report'] = df[report_col].apply(preprocess_text)

    # 按部门分组
    grouped = df.groupby(department_col)

    # 准备结果DataFrame
    result_df = df.copy()
    result_df['聚类ID'] = -1  # 初始化为-1表示未聚类
    result_df['代表性报表'] = ""  # 代表性报表名称

    # 对每个部门进行处理
    for department, group in grouped:
        # report_names = group['processed_report'].tolist()
        report_names = group[report_col].tolist()
        original_names = group[report_col].tolist()
        indices = group.index.tolist()

        print(f"处理部门: {department}, 报表数量: {len(report_names)}")

        # 如果只有一个报表，直接处理
        if len(report_names) == 1:
            result_df.loc[indices[0], '聚类ID'] = 0
            result_df.loc[indices[0], '代表性报表'] = original_names[0]
            continue

        # 文本向量化
        vectorizer = TfidfVectorizer(min_df=1, norm='l2')
        try:
            X = vectorizer.fit_transform(report_names)
            X_array = X.toarray()
        except Exception as e:
            print(f"向量化时出错: {e}")
            # 如果向量化失败，将每个报表作为单独一类
            for i, idx in enumerate(indices):
                result_df.loc[idx, '聚类ID'] = i
                result_df.loc[idx, '代表性报表'] = original_names[i]
            continue

        # 确定聚类数量（最多50个聚类） --1648
        n_clusters = min(30, len(report_names))

        # 使用KMeans进行聚类
        try:
            kmeans = KMeans(n_clusters=n_clusters, random_state=42, n_init=10)
            clusters = kmeans.fit_predict(X_array)
        except Exception as e:
            print(f"聚类时出错: {e}")
            # 如果聚类失败，将每个报表作为单独一类
            for i, idx in enumerate(indices):
                result_df.loc[idx, '聚类ID'] = i
                result_df.loc[idx, '代表性报表'] = original_names[i]
            continue

        # 为每个聚类找到代表性报表
        cluster_reports = defaultdict(list)
        cluster_vectors = defaultdict(list)

        for i, cluster_id in enumerate(clusters):
            cluster_reports[cluster_id].append(original_names[i])
            cluster_vectors[cluster_id].append(X_array[i])

        # 为每个聚类找到代表性报表
        representative_reports = {}
        for cluster_id, reports in cluster_reports.items():
            vectors = cluster_vectors[cluster_id]
            representative = find_centroid_report(reports, vectorizer, vectors)
            representative_reports[cluster_id] = representative

        # 更新结果DataFrame
        for i, idx in enumerate(indices):
            cluster_id = clusters[i]
            result_df.loc[idx, '聚类ID'] = cluster_id
            result_df.loc[idx, '代表性报表'] = representative_reports[cluster_id]

    return result_df


def save_results_to_excel(original_df, result_df, file_path, original_sheet_name="原始数据",
                          result_sheet_name="聚类结果"):
    """将原始数据和聚类结果保存到Excel文件的不同Sheet中"""
    try:
        # 使用ExcelWriter创建包含多个Sheet的Excel文件
        with pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
            # 保存原始数据
            original_df.to_excel(writer, sheet_name=original_sheet_name, index=False)
            # 保存聚类结果
            result_df.to_excel(writer, sheet_name=result_sheet_name, index=False)

        print(f"结果已成功保存到Excel文件的'{result_sheet_name}'Sheet中")
        return True
    except Exception as e:
        print(f"保存结果时出错: {e}")
        # 如果追加模式失败，尝试创建新文件
        try:
            with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
                original_df.to_excel(writer, sheet_name=original_sheet_name, index=False)
                result_df.to_excel(writer, sheet_name=result_sheet_name, index=False)
            print(f"结果已成功保存到Excel文件的'{result_sheet_name}'Sheet中")
            return True
        except Exception as e2:
            print(f"创建新文件时也出错: {e2}")
            return False


# 使用示例
if __name__ == "__main__":
    # 替换为您的Excel文件路径
    file_path = "C:/Users/xingwenzheng/Desktop/国家部委-附件1-基层报表底数初步清单-1.xlsx"

    # 处理Excel文件
    original_df = pd.read_excel(file_path)
    result_df = process_excel_with_clustering(
        file_path,
        sheet_name="分割前数据",  # 根据实际sheet名称修改  汇总部门
        department_col="部门名称",  # 根据实际列名调整   匹配部门   报表名称
        report_col="报表名称"  # 根据实际列名调整        报表名称   数据项
    )

    if result_df is not None:
        # 显示前几行结果
        print("聚类结果预览:")
        print(result_df.head())

        # 保存结果到Excel文件的新Sheet中
        success = save_results_to_excel(original_df, result_df, file_path)

        if not success:
            # 如果保存失败，尝试保存到新文件
            new_file_path = "C:/Users/xingwenzheng/Desktop/1.xlsx"
            with pd.ExcelWriter(new_file_path, engine='openpyxl') as writer:
                original_df.to_excel(writer, sheet_name="原始数据", index=False)
                result_df.to_excel(writer, sheet_name="聚类结果", index=False)
            print(f"结果已保存到新文件: {new_file_path}")
    else:
        print("未能处理Excel文件，请检查文件路径和格式")

